<?php

class mobileapi_rpc_statistic {

    function __construct($app) {
        $this->app = $app;
    }

    function get_goods_category() {
        $parent_id = isset($_POST['parent_id']) ? intval($_POST['parent_id']) : 0;
        $cat_list = kernel::database()->select("SELECT cat_id,cat_name FROM sdb_b2c_goods_cat WHERE parent_id={$parent_id}");
        return $cat_list;
    }

    function get_goods_brand() {
        $brand_list = kernel::database()->select("SELECT brand_id,brand_name FROM sdb_b2c_brand WHERE disabled='false'");
        return $brand_list;
    }

    function circle_data() {
        $start = $_POST['start'];
        $end = $_POST['end'];
        $cat_id = $_POST['cat_id'];
        $cat_level = $_POST['cat_level'];
        $brand_id = $_POST['brand_id'];
        $goods_name = $_POST['goods_name'];
        $goods_status = $_POST['goods_status'];
        $user_name = $_POST['user_name'];
        $platform = $_POST['platform'];


        // 推荐
        $where = " (sdb_b2c_goods_opinions.created>={$start} AND sdb_b2c_goods_opinions.created<={$end} AND sdb_b2c_goods_opinions.status='normal') ";
        // 商品分类
        if ($cat_level == 3) {
            $where .= " AND (sdb_b2c_goods_cat.cat_id={$cat_id}) ";
        } else if ($cat_level == 2) {
            $where .= " AND (sdb_b2c_goods_cat.cat_id={$cat_id} OR  sdb_b2c_goods_cat.parent_id={$cat_id}) ";
        } else {
            if ($cat_id != 0) {
                $where .= " AND (sdb_b2c_goods_cat.cat_id={$cat_id} OR  sdb_b2c_goods_cat.parent_id={$cat_id} OR sdb_b2c_goods_cat.parent_id in(SELECT cat_id FROM sdb_b2c_goods_cat WHERE parent_id={$cat_id})) ";
            }
        }
        // 用户
        if ($platform) {
            if ($platform == 'android') {
                $where .= " AND (sdb_b2c_members.source='api' AND source_app!='') ";
            } else if ($platform == 'ios') {
                $where .= " AND (sdb_b2c_members.source='api' AND source_app='') ";
            } else {
                $where .= " AND (sdb_b2c_members.source='{$platform}') ";
            }
        }
        if ($user_name) {
            $where .= " AND (sdb_b2c_members.name LIKE '%{$user_name}%') ";
        }
//        // 商品
        if ($goods_name) {
            $where .= " AND (sdb_b2c_goods.name LIKE '%{$goods_name}%') ";
        }
        if ($goods_status == 1) {
            $where .= " AND (sdb_b2c_goods.marketable='true') ";
        } else if ($goods_status == 2) {
            $where .= " AND (sdb_b2c_goods.marketable='false') ";
        }
        //品牌
        if ($brand_id) {
            $where .= " AND (sdb_b2c_goods.brand_id={$brand_id}) ";
        }

        $field = 'sdb_b2c_goods_opinions.id opinions_id,sdb_b2c_goods_opinions.p_num,sdb_b2c_goods_opinions.created,
            sdb_b2c_members.name user_name,sdb_b2c_members.fans_num,sdb_b2c_members.opinions_num,
            sdb_b2c_goods.bn goods_bn,sdb_b2c_goods.name goods_name,
            sdb_b2c_brand.brand_name,
            sdb_b2c_goods_cat.cat_name';
        $sql = "SELECT {$field} FROM sdb_b2c_goods_opinions
          LEFT JOIN sdb_b2c_members ON sdb_b2c_members.member_id=sdb_b2c_goods_opinions.member_id
          LEFT JOIN sdb_b2c_goods ON sdb_b2c_goods.goods_id=sdb_b2c_goods_opinions.goods_id
          LEFT JOIN sdb_b2c_brand ON sdb_b2c_brand.brand_id=sdb_b2c_goods.brand_id
          LEFT JOIN sdb_b2c_goods_cat ON sdb_b2c_goods_cat.cat_id=sdb_b2c_goods.cat_id
          WHERE {$where}
          ORDER BY sdb_b2c_goods_opinions.id DESC";
//return array($sql);
        $result = kernel::database()->select($sql);
        return $result;
    }

    public function platform_data() {
        $start = $_POST['start'];
        $end = $_POST['end'];
        $field = "sdb_b2c_orders.order_id,sdb_b2c_orders.payed,sdb_b2c_orders.pay_status,
            sdb_b2c_orders.createtime,sdb_b2c_orders.createtime,
            sdb_b2c_members.member_id,sdb_b2c_members.name user_name,sdb_b2c_members.source,sdb_b2c_members.source_app,
            (SELECT if(count(*)>0,0,1) FROM sdb_b2c_orders WHERE sdb_b2c_orders.member_id=sdb_b2c_members.member_id AND
            sdb_b2c_orders.pay_status in('1','2') AND sdb_b2c_orders.createtime<{$start}) is_new_user";
        $sql = "SELECT {$field} FROM sdb_b2c_orders
          LEFT JOIN sdb_b2c_members ON sdb_b2c_members.member_id=sdb_b2c_orders.member_id
          WHERE sdb_b2c_orders.pay_status in('1','2')
          AND sdb_b2c_orders.createtime>={$start} AND sdb_b2c_orders.createtime<{$end}";
        $order_data = kernel::database()->select($sql);

        $user_data = kernel::database()->select("SELECT member_id,regtime,source,source_app FROM sdb_b2c_members WHERE regtime>={$start} AND regtime<={$end}");

        return array(
            'order_data' => $order_data,
            'user_data' => $user_data,
        );
    }

    public function channel_data() {
        $start = $_POST['start'];
        $end = $_POST['end'];
        // TODO 渠道来源字段是否需要更改
        $field = "sdb_b2c_orders.order_id,sdb_b2c_orders.payed,sdb_b2c_orders.pay_status,
            sdb_b2c_orders.createtime,sdb_b2c_orders.createtime,
            sdb_b2c_members.member_id,sdb_b2c_members.name user_name,sdb_b2c_members.source_app channel,
            (SELECT if(count(*)>0,0,1) FROM sdb_b2c_orders WHERE sdb_b2c_orders.member_id=sdb_b2c_members.member_id AND
            sdb_b2c_orders.pay_status in('1','2') AND sdb_b2c_orders.createtime<{$start}) is_new_user";
        $sql = "SELECT {$field} FROM sdb_b2c_orders
          LEFT JOIN sdb_b2c_members ON sdb_b2c_members.member_id=sdb_b2c_orders.member_id
          WHERE sdb_b2c_orders.pay_status in('1','2')
          AND sdb_b2c_orders.createtime>={$start} AND sdb_b2c_orders.createtime<{$end}";
        $order_data = kernel::database()->select($sql);

        $user_data = kernel::database()->select("SELECT member_id,regtime,source_app channel FROM sdb_b2c_members WHERE regtime>={$start} AND regtime<={$end}");

        return array(
            'sql' => $sql,
            'sql1' => "SELECT member_id,regtime,source_app channel FROM sdb_b2c_members WHERE regtime>={$start} AND regtime<={$end}",
            'order_data' => $order_data,
            'user_data' => $user_data,
        );
    }

    public function gross_profit() {
        $start = $_POST['start'];
        $end = $_POST['end'];
        $date = date('Ymd', $start);

        // TODO createtime 用的开始的查询日期
        $order_id_sql = "SELECT order_id FROM sdb_b2c_orders
            WHERE pay_status IN ('1', '2') AND createtime>={$start} AND createtime<{$end} AND payed!=0";
        $order_id_list = kernel::database()->select($order_id_sql);
        $order_ids = array();
        foreach ($order_id_list as $row) {
            $order_ids[] = $row['order_id'];
        }
        $order_ids = '\'' . implode('\',\'', $order_ids) . '\'';
        $order_payed_sql = "SELECT sum(payed) sales FROM sdb_b2c_orders WHERE order_id in($order_ids)";
        $order_payed = kernel::database()->selectrow($order_payed_sql);
        $order_goods_sql = "SELECT sum(nums) goods_number,sum(cost*nums) total_purchase
            FROM sdb_b2c_order_items WHERE order_id in($order_ids)";
        $order_goods = kernel::database()->select($order_goods_sql);
        $order_result = array(
            'sales' => $order_payed,
            'order_number' => count($order_id_list),
            'goods_number' => $order_goods[0],
            'total_purchase' => $order_goods[1],
            'createtime' => $date,
        );
//        return $order_result;
//        return array($order_payed, $order_goods);
//        $order_sql = "SELECT count(DISTINCT sdb_b2c_orders.order_id) order_number,
//            sum(payed) sales,sum(nums) goods_number,sum(cost*nums) total_purchase,{$date} createtime
//            FROM sdb_b2c_orders
//            LEFT JOIN sdb_b2c_order_items ON sdb_b2c_orders.order_id = sdb_b2c_order_items.order_id
//            WHERE pay_status IN ('1', '2') AND createtime>={$start} AND createtime<{$end} AND payed!=0";
//        $order_result = kernel::database()->select($order_sql);
//        return $order_result;

        $last_week_start = $end - 7 * 86400;
        $last_week_end = $end;

        $last_month_day_start = $end - 30 * 86400;
        $last_month_day_end = $end;

        $all_start = 0;
        $all_end = $end;

        $last_week_sql = "SELECT sum(nums) sale_number FROM sdb_b2c_orders
            LEFT JOIN sdb_b2c_order_items ON sdb_b2c_orders.order_id = sdb_b2c_order_items.order_id
            WHERE pay_status IN ('1', '2')
            AND createtime>={$last_week_start} AND createtime<{$last_week_end}";
        $last_month_day_sql = "SELECT sum(nums) sale_number FROM sdb_b2c_orders
            LEFT JOIN sdb_b2c_order_items ON sdb_b2c_orders.order_id = sdb_b2c_order_items.order_id
            WHERE pay_status IN ('1', '2')
            AND createtime>={$last_month_day_start} AND createtime<{$last_month_day_end}";
        $all_sql = "SELECT sum(nums) sale_number FROM sdb_b2c_orders
            LEFT JOIN sdb_b2c_order_items ON sdb_b2c_orders.order_id = sdb_b2c_order_items.order_id
            WHERE pay_status IN ('1', '2')
            AND createtime>={$all_start} AND createtime<{$all_end}";

        $last_week_data = kernel::database()->select($last_week_sql);
        $last_month_day_data = kernel::database()->select($last_month_day_sql);
        $all_data = kernel::database()->select($all_sql);

        $product_sql = 'SELECT
            COUNT(*) sku_number,
            SUM(store) + SUM(freez) total_store,
            SUM(store*cost)+SUM(freez*cost) total_purchase,
            SUM(price*store)+SUM(price*freez) total_sales
            FROM sdb_b2c_products';
        $product_result = kernel::database()->select($product_sql);
        return array(
            'order' => array(
                'order_data' => $order_result[0],
                'last_week_sale' => $last_week_data[0],
                'last_month_day_sale' => $last_month_day_data[0],
                'all_sale' => $all_data[0],
                'total_store' => $product_result[0]['total_store'],
            ),
            'product' => $product_result[0],
        );
    }

    public function gross_profit_detail() {
        $start = $_POST['start'];
        $end = $_POST['end'];
        $cat_id = $_POST['cat_id'];
        $cat_level = $_POST['cat_level'];
        $goods_name = $_POST['goods_name'];

        // 商品
        $goods_join = '';
        $where_and = '';
        if ($goods_name) {
            $goods_join = "RIGHT JOIN (SELECT DISTINCT(order_id) FROM sdb_b2c_order_items WHERE `name` LIKE '%{$goods_name}%') sboi ON sboi.order_id=sdb_b2c_orders.order_id";
            $where_and .= " AND (sdb_b2c_goods.name LIKE '%{$goods_name}%') ";
        }
        $cat_join= '';
        if ($cat_level == 3) {
            $cat_join = "RIGHT JOIN
                (SELECT DISTINCT(order_id) FROM sdb_b2c_order_items
                    INNER JOIN sdb_b2c_goods ON sdb_b2c_goods.goods_id=sdb_b2c_order_items.goods_id
                    INNER JOIN sdb_b2c_goods_cat ON sdb_b2c_goods_cat.cat_id=sdb_b2c_goods.cat_id
                    WHERE sdb_b2c_goods_cat.cat_id={$cat_id}) ct ON ct.order_id=sdb_b2c_orders.order_id";
            $where_and .= " AND (sdb_b2c_goods_cat.cat_id={$cat_id}) ";
        } else if ($cat_level == 2) {
            $cat_join = "RIGHT JOIN
                (SELECT DISTINCT(order_id) FROM sdb_b2c_order_items
                    INNER JOIN sdb_b2c_goods ON sdb_b2c_goods.goods_id=sdb_b2c_order_items.goods_id
                    INNER JOIN sdb_b2c_goods_cat ON sdb_b2c_goods_cat.cat_id=sdb_b2c_goods.cat_id
                    WHERE sdb_b2c_goods_cat.cat_id={$cat_id} OR  sdb_b2c_goods_cat.parent_id={$cat_id})
                ct ON ct.order_id=sdb_b2c_orders.order_id";
            $where_and .= " AND (sdb_b2c_goods_cat.cat_id={$cat_id} OR  sdb_b2c_goods_cat.parent_id={$cat_id}) ";
        } else {
            if ($cat_id != 0) {
                $cat_join = "RIGHT JOIN
                (SELECT DISTINCT(order_id) FROM sdb_b2c_order_items
                    INNER JOIN sdb_b2c_goods ON sdb_b2c_goods.goods_id=sdb_b2c_order_items.goods_id
                    INNER JOIN sdb_b2c_goods_cat ON sdb_b2c_goods_cat.cat_id=sdb_b2c_goods.cat_id
                    WHERE sdb_b2c_goods_cat.cat_id={$cat_id} OR  sdb_b2c_goods_cat.parent_id={$cat_id}
                    OR sdb_b2c_goods_cat.parent_id in
                        (SELECT cat_id FROM sdb_b2c_goods_cat WHERE parent_id={$cat_id}))
                ct ON ct.order_id=sdb_b2c_orders.order_id";
                $where_and .= " AND (sdb_b2c_goods_cat.cat_id={$cat_id} OR  sdb_b2c_goods_cat.parent_id={$cat_id} OR sdb_b2c_goods_cat.parent_id in(SELECT cat_id FROM sdb_b2c_goods_cat WHERE parent_id={$cat_id})) ";
            }
        }

        $order_sql = "SELECT sdb_b2c_orders.order_id,
            sdb_b2c_orders.payed,
            sdb_b2c_order_items.nums goods_number,
            sdb_b2c_order_items.cost purchase,
            sdb_b2c_orders.createtime
            FROM sdb_b2c_orders
            {$goods_join}
            {$cat_join}
            LEFT JOIN sdb_b2c_order_items ON sdb_b2c_orders.order_id = sdb_b2c_order_items.order_id
            WHERE pay_status IN ('1', '2') AND createtime>={$start} AND createtime<{$end}";
        $order_result = kernel::database()->select($order_sql);


        $all_sql = "SELECT sdb_b2c_order_items.nums sale_number,sdb_b2c_orders.createtime
            FROM sdb_b2c_orders
            {$goods_join}
            {$cat_join}
            LEFT JOIN sdb_b2c_order_items ON sdb_b2c_orders.order_id = sdb_b2c_order_items.order_id
            WHERE pay_status IN ('1', '2')
            AND createtime<{$end}";

        $dsr_data = kernel::database()->select($all_sql);

        // TODO 无进货记录无法按日期查询库存等信息
        $product_sql = 'SELECT sum(sdb_b2c_products.store)+sum(sdb_b2c_products.freez) goods_number,
            count(DISTINCT sdb_b2c_products.product_id) sku_number,
            sum(sdb_b2c_products.cost*sdb_b2c_products.store)+sum(sdb_b2c_products.cost*sdb_b2c_products.freez) total_purchase,
            sum(sdb_b2c_products.price*sdb_b2c_products.store)+sum(sdb_b2c_products.price*sdb_b2c_products.freez) total_sales
            FROM sdb_b2c_products
            LEFT JOIN sdb_b2c_goods ON sdb_b2c_products.goods_id=sdb_b2c_goods.goods_id
            LEFT JOIN sdb_b2c_goods_cat ON sdb_b2c_goods_cat.cat_id=sdb_b2c_goods.cat_id';
        if ($where_and != '') {
            $product_sql .= " WHERE 1 {$where_and}";
        }
        $product_result = kernel::database()->selectrow($product_sql);

        return array(
            'order_data' => $order_result,
            'product_data' => $product_result,
            'dsr_data' => $dsr_data,
        );
    }

}
